﻿using System.Collections.Generic;
using System.Data;
using Weight.Base;
using Weight.Model;
using Dapper;
using System.Text;
using Dapper.Contrib.Extensions;

namespace Weight.Dal
{
    public class DalMenu
    {
        /// <summary>
        /// 获取菜单项
        /// </summary>
        /// <param name="name">菜单名称</param>
        /// <returns></returns>
       public IEnumerable<MenuEntity> GetMenu(string name)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @"with temp as(select * ,0 as level,MenuName as PMenuName from Menu where pid='/'
                union all
                select c.* ,t.level + 1 as level,t.MenuName as PMenuName from Menu c ,temp t where c.pid = t.id)
                select* from temp  ";
                if(!string.IsNullOrEmpty(name))
                {
                    name = "%" + name + "%";
                    sql += " where MenuName like @name or pid='/'";
                }
                sql += " order by  MenuName desc ";

                return conn.Query<MenuEntity>(sql,new { name=name});
            }
        }

        /// <summary>
        /// 获取第一级别菜单
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MenuEntity> GetFirstLevelMenu(int level)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @"with temp as(select * ,0 as level,MenuName as PMenuName from Menu where pid='/'
                union  all
                select c.* ,t.level+1 as level,t.MenuName as PMenuName from Menu  c ,temp t where c.pid=t.id)
                select * from temp  where level>@level order by Sort asc ";

                return conn.Query<MenuEntity>(sql,new { level});
            }
        }

       /// <summary>
       /// 添加菜单项
       /// </summary>
       /// <param name="entity"></param>
       /// <returns></returns>
        public  bool Add(MenuEntity entity)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @" insert into Menu(Id,PId,MenuName,PageName,IsShow,Sort,ImgPath)values(@Id,@PId,@MenuName,@PageName,@IsShow,@Sort,@ImgPath)";
                return conn.Execute(sql,entity) > 0 ? true:false;
            }
        }

        /// <summary>
        /// 修改菜单
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Update(MenuEntity entity)
        {
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @" update Menu set MenuName=@MenuName,PageName=@PageName,IsShow=@IsShow, Sort=@Sort,ImgPath = @ImgPath where Id=@Id";
                return conn.Execute(sql, entity) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 添加菜单项
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Add(List<MenuEntity> entitys)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @" insert into Menu(Id,PId,MenuName,PageName,IsShow,Sort)values(@Id,@PId,@MenuName,@PageName,@IsShow,@Sort)";
                return conn.Execute(sql, entitys) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 判断菜单名称是否已经存在
        /// </summary>
        /// <param name="name">菜单名称</param>
        /// <param name="pName">父节点名称</param>
        /// <returns></returns>
        public bool NameIsExist(string name, string pName)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @"with temp as(select * ,0 as level,MenuName as PMenuName from Menu where pid='/'
                union all
                select c.* ,t.level + 1 as level,t.MenuName as PMenuName from Menu c ,temp t where c.pid = t.id)
                select count(1) from temp  ";
                sql += " where MenuName=@name and  PMenuName=@pName";
                return conn.ExecuteScalar<int>(sql, new { name = name, pName }) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 获取儿子和孙子菜单通过Pid
        /// </summary>
        /// <param name="pid">父节点Id</param>
        /// <returns></returns>
        public IEnumerable<MenuEntity> GetMenusByPId(string pid)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @"with temp as(select * ,0 as level,MenuName as PMenuName from Menu where pid=@pid
                union  all
                select c.* ,t.level+1 as level,t.MenuName as PMenuName from Menu  c ,temp t where c.pid=t.id)
                select * from temp   order by  MenuName desc ";

                return conn.Query<MenuEntity>(sql, new { pid });
            }
        }



        /// <summary>
        /// 获取子菜单通过Pid
        /// </summary>
        /// <param name="pid">父节点Id</param>
        /// <returns></returns>
        public IEnumerable<MenuEntity> GetMenuByPId(string pid)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @"with temp as(select * ,0 as level,MenuName as PMenuName from Menu where pid=@pid
                union  all
                select c.* ,t.level+1 as level,t.MenuName as PMenuName from Menu  c ,temp t where c.pid=t.id)
                select * from temp where level=0  order by  MenuName desc ";

                return conn.Query<MenuEntity>(sql, new { pid });
            }
        }

        /// <summary>
        /// 获取菜单通过id
        /// </summary>
        /// <param name="id">菜单id</param>
        /// <returns></returns>
        public MenuEntity GetMenuById(string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @" select * from  Menu where id=@id  ";
                return conn.QueryFirstOrDefault<MenuEntity>(sql, new { id });
            }
        }

        /// <summary>
        /// 获取菜单通过菜单名称
        /// </summary>
        /// <param name="name">菜单名称</param>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public IEnumerable< MenuEntity> GetMenuByName(string name,string pName,string id="")
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @"with temp as(select * ,0 as level,MenuName as PMenuName from Menu where pid='/'
                union all
                select c.* ,t.level + 1 as level,t.MenuName as PMenuName from Menu c ,temp t where c.pid = t.id)
                select* from temp  ";
                sql += " where MenuName=@name and  PMenuName=@pName and id<>@id";
                return conn.Query<MenuEntity>(sql, new { name = name, pName,id });
            }

        }

        /// <summary>
        /// 删除菜单
        /// </summary>
        /// <param name="id">菜单Id</param>
        /// <returns></returns>
        public bool DeleteById(string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @"with temp as(select * ,0 as level,MenuName as PMenuName from Menu where Id=@id
                union all
                select c.* ,t.level + 1 as level,t.MenuName as PMenuName from Menu c ,temp t where c.pid = t.id)
                delete from Menu where  Pid in (select id from temp ) or Id=@id";
               return  conn.Execute(sql, new { id }) > 0 ? true:false;
            }
        }

    }
}
